﻿Imports System.Data.Linq
Imports System.Linq

Public Class MOilPayments
    Private db As New TanNamChinhDataContext
    Public Function getPaymentList1() As Table(Of tblOilPayment)
        Try
            Return db.tblOilPayments
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetPaymentList2() As IQueryable(Of tblOilPayment)
        Try
            Dim query = From b In db.tblOilPayments
                        Order By b.DatePayment Descending
                        Select b
            'Return (From r In db.tblCurrencies
            '           Select r).Single
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetPaymentList2(ByVal thang As Integer, ByVal quy As Integer, ByVal nam As Integer) As IQueryable(Of tblOilPayment)
        Dim query
        Try

            If thang <> 0 And quy = 0 And nam <> 0 Then
                query = From b In db.tblOilPayments
                                      Where b.DatePayment.Value.Month = thang And b.DatePayment.Value.Year = nam
                                        Order By b.ID_OilPayment Descending
                                        Select b
            End If
            If thang = 0 And quy <> 0 And nam <> 0 Then
                'Theo quý
                Dim first = 0
                Dim last = 0
                Select Case quy
                    Case 1
                        'nếu quý 1 tháng 1,2,3
                        first = 1
                        last = 3
                    Case 2
                        first = 4
                        last = 6
                    Case 3
                        first = 7
                        last = 9
                    Case 4
                        first = 10
                        last = 12
                End Select

                query = From b In db.tblOilPayments
                        Where b.DatePayment.Value.Month >= first And b.DatePayment.Value.Month <= last And b.DatePayment.Value.Year = nam
                         Order By b.ID_OilPayment Descending
                        Select b
            End If
            If thang <> 0 And quy = 0 And nam = 0 Then
                'Theo tháng thì năm hiện tại
                query = From b In db.tblOilPayments
                        Where b.DatePayment.Value.Month = thang And b.DatePayment.Value.Year = DateTime.Now.Year
                        Select b
            ElseIf thang = 0 And quy <> 0 And nam = 0 Then
                'Theo quý
                Dim first = 0
                Dim last = 0
                Select Case quy
                    Case 1
                        'nếu quý 1 tháng 1,2,3
                        first = 1
                        last = 3
                    Case 2
                        first = 4
                        last = 6
                    Case 3
                        first = 7
                        last = 9
                    Case 4
                        first = 10
                        last = 12
                End Select

                query = From b In db.tblOilPayments
                        Where b.DatePayment.Value.Month >= first And b.DatePayment.Value.Month <= last And b.DatePayment.Value.Year = DateTime.Now.Year
                        Select b
            ElseIf thang = 0 And quy = 0 And nam <> 0 Then
                'Theo năm
                query = From b In db.tblOilPayments
                        Where b.DatePayment.Value.Year = nam
                        Select b
            End If


            'Return (From r In db.tblCurrencies
            '           Select r).Single
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    'Public Function getPaymentMonthList(ByVal month As Integer, ByVal year As Integer) As IQueryable(Of tblTotalMonth)
    '    Dim query = Nothing
    '    Try

    '        If month = 0 And year = 0 Then
    '            query = From b In db.tblTotalMonths
    '                    Order By b.Year Descending, b.Month Descending
    '                    Select b
    '            Return query
    '        ElseIf month = 0 Then
    '            '     Dim query = From b In db.tblTotalMonths
    '            '        Where b.Year = year
    '            'Select b.ID, b.Month, b.TotalPayment, b.PreviousMonth, b.Discount, b.TotalLiter, b.TotalMoneyPush, b.ExcessMonth
    '            'Order By Month Ascending
    '            query = From b In db.tblTotalMonths
    '                     Order By b.Year Descending, b.Month Descending
    '              Where b.Year = year
    '             Select b
    '        ElseIf month <> 0 Then
    '            'nếu có tháng nhập vào
    '            query = From b In db.tblTotalMonths
    '                    Where b.Year = year And b.Month = month
    '                    Order By b.Year Descending, b.Month Descending
    '                    Select b
    '        End If



    '        'Return (From r In db.tblCurrencies
    '        '           Select r).Single

    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    Return query
    'End Function
    Public Function getPreviousAmount(ByVal month As Integer, ByVal year As Integer) As Integer
        'Kiểm tra xem có cùng năm hay không
        Dim currentYear As Integer = DateTime.Now.Year
        Dim currentMonth As Integer = DateTime.Now.Month
        Dim sums As Double
        'If currentYear = year Then
        '    'Nếu cùng năm thì kiểm tra tháng

        If month = 1 Then
            'Tháng đầu năm thì phải lấy tháng 12 năm trước đó
            Try
                sums = (From p In db.tblTotalMonths _
                        Where p.Month = 12 And p.Year = year - 1
                        Select CInt(p.ExcessMonth.Value)).Sum()
            Catch ex As Exception
                Throw ex
            End Try
        Else
            'Nếu tháng trong năm thì lấy tháng trước đó
            Try
                sums = (From p In db.tblTotalMonths _
                        Where p.Month = month - 1 And p.Year = year
                        Select CInt(p.ExcessMonth)).Sum()
            Catch ex As Exception
                Throw ex
            End Try
        End If
        'ElseIf 
        'nếu mà k cùng năm
        'End If



        Return sums
    End Function
    Public Function getSumofMoneyPayment(ByVal month As Integer, ByVal year As Integer) As Integer
        Dim sums As Integer
        Try
            sums = (From p In db.tblOilPayments _
                    Where p.DatePayment.Value.Month = month And p.DatePayment.Value.Year = year
                    Select CInt(p.Amount.Value)).Sum()
        Catch ex As Exception
            Throw ex
        End Try
        Return sums
    End Function
    Public Sub InsertNewRecord(ByVal obj As tblOilPayment)
        'db.tblOilPayments.InsertOnSubmit(obj)
        db.GetTable(Of tblOilPayment)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub
    Public Sub UpdateRecord(ByVal obj As tblOilPayment)


        'If obj.ID_OilPayment = 0 Then

        '    InsertNewRecord(obj)

        'Else
        Dim getData = (From b In db.tblOilPayments
                                Where b.ID_OilPayment = obj.ID_OilPayment
                                Select b).Single()
        getData.Amount = obj.Amount
        getData.Method = obj.Method
        getData.DatePayment = obj.DatePayment
        getData.Description = obj.Description
        db.SubmitChanges()
        'End If



    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Dim getData = (From b In db.tblOilPayments
                                Where b.ID_OilPayment = id
                                Select b).Single()
        db.tblOilPayments.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Function IsExistTotalMonth(ByVal month As Integer, ByVal year As Integer) As Integer
        Dim ids As Integer = 0
        Try
            ids = (From b In db.tblTotalMonths
                 Where b.Month = month And b.Year = year
                 Select b.ID).First()

        Catch ex As Exception
            Return 0
        End Try
        Return ids
    End Function
End Class
